package cn.itcast.service.impl;

import cn.itcast.bean.QuotRes;
import cn.itcast.constant.HttpCode;
import cn.itcast.mapper.QuotMapper;
import cn.itcast.service.QuotService;
import cn.itcast.util.DateUtil;
import cn.itcast.util.DbUtil;
import cn.itcast.util.HbaseUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.annotations.Mapper;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class QuotServiceImpl implements QuotService {

    //druid.url = jdbc:avatica:remote:url=http://node01:8888/druid/v2/sql/avatica/
    //druid.driverClassName= org.apache.calcite.avatica.remote.Driver
    @Value("${druid.url}")
    private String url;
    @Value("${druid.driverClassName}")
    private String driverClassName;

    @Autowired
    private QuotMapper quotMapper;

    //国内指数查询
    @Override
    public QuotRes indexQuery() throws SQLException {
        //获取druid连接对象
        Connection conn = DbUtil.getConn(driverClassName, url);
        Statement statement = conn.createStatement();
        String sql = "SELECT indexCode,indexName,preClosePrice,openPrice,closePrice,\n" +
                "ROUND(CAST(closePrice as DOUBLE) - CAST(preClosePrice as DOUBLE),2) as updown,\n" +
                "round((CAST(closePrice as DOUBLE) - CAST(preClosePrice as DOUBLE))/CAST(preClosePrice as DOUBLE),2) as increase,\n" +
                "tradeAmtDay,\n" +
                "tradeVolDay\n" +
                "FROM \"index_stream_sse\"\n" +
                "WHERE \"__time\" >= CURRENT_TIMESTAMP - INTERVAL '7' DAY\n" +
                "ORDER BY __time desc \n" +
                "LIMIT 10";
        ResultSet resultSet = statement.executeQuery(sql);
        List<Map<String, Object>> list = new ArrayList<>();
        while (resultSet.next()){
            Map<String, Object> map = new HashMap<>();
            //apache druid 返回的数据都是字符串类型
            map.put("code",resultSet.getString("indexCode"));
            map.put("name",resultSet.getString("indexName"));
            map.put("preClosePrice",resultSet.getString("preClosePrice"));
            map.put("openPrice",resultSet.getString("openPrice"));
            map.put("tradePrice",resultSet.getString("closePrice"));
            map.put("upDown",resultSet.getDouble("updown"));
            map.put("increase",resultSet.getDouble("increase"));
            map.put("tradeAmt",resultSet.getString("tradeAmtDay"));
            map.put("tradeVol",resultSet.getString("tradeVolDay"));
            list.add(map);
        }

        //关流
        DbUtil.close(resultSet,statement,conn);

        QuotRes quotRes = new QuotRes();
        quotRes.setItems(list);
        quotRes.setCode(HttpCode.SUCC_200.getCode());
        return quotRes;
    }


    //板块指数查询
    @Override
    public QuotRes sectorQuery() throws SQLException {
        Connection conn = DbUtil.getConn(driverClassName, url);
        Statement statement = conn.createStatement();
        String sql = "SELECT sectorCode,sectorName,preClosePrice,openPrice,closePrice,tradeAmtDay,tradeVolDay\n" +
                "FROM \"sector_stream\"\n" +
                "WHERE \"__time\" >= CURRENT_TIMESTAMP - INTERVAL '7' DAY\n" +
                "ORDER BY __time DESC\n" +
                "LIMIT 10 ";
        ResultSet resultSet = statement.executeQuery(sql);
        List<Map<String, Object>> list = new ArrayList<>();
        while (resultSet.next()){
            HashMap<String, Object> map = new HashMap<>();
            map.put("code",resultSet.getString(1));
            map.put("name",resultSet.getString(2));
            map.put("preClosePrice",resultSet.getString(3));
            map.put("openPrice",resultSet.getString(4));
            map.put("tradePrice",resultSet.getString(5));
            map.put("tradeAmt",resultSet.getString(6));
            map.put("tradeVol",resultSet.getString(7));
            list.add(map);
        }
        //关流
        DbUtil.close(resultSet,statement,conn);

        QuotRes quotRes = new QuotRes();
        quotRes.setItems(list);
        quotRes.setCode(HttpCode.SUCC_200.getCode()); //表示成功
        return quotRes;
    }


    //涨幅榜查询
    @Override
    public QuotRes increaseQuery() throws SQLException {
        Connection conn = DbUtil.getConn(driverClassName, url);
        Statement stat = conn.createStatement();
        String sql = "SELECT secCode,secName,increase,tradePrice,updown,\n" +
                "       tradeVol,amplitude,preClosePrice,tradeAmt\n" +
                "FROM \"stock_stream_increase\"\n" +
                "WHERE \"__time\" >= CURRENT_TIMESTAMP - INTERVAL '7' DAY\n" +
                "ORDER BY __time desc \n" +
                "LIMIT 10";
        ResultSet resultSet = stat.executeQuery(sql);
        ArrayList<Map<String, Object>> list = new ArrayList<>();
        while (resultSet.next()){
            HashMap<String, Object> map = new HashMap<>();
            map.put("code",resultSet.getString(1));
            map.put("name",resultSet.getString(2));
            map.put("increase",resultSet.getString(3));
            map.put("tradePrice",resultSet.getString(4));
            map.put("upDown",resultSet.getString(5));
            map.put("tradeVol",resultSet.getString(6));
            map.put("amplitude",resultSet.getString(7));
            map.put("preClosePrice",resultSet.getString(8));
            map.put("tradeAmt",resultSet.getString(9));
            list.add(map);
        }
        //关流
        DbUtil.close(resultSet,stat,conn);

        QuotRes quotRes = new QuotRes();
        quotRes.setItems(list);
        quotRes.setCode(HttpCode.SUCC_200.getCode());
        return quotRes;
    }


    //涨停跌停数
    @Override
    public JSONObject upDownCount() throws SQLException {
        Connection conn = DbUtil.getConn(driverClassName, url);
        Statement stat = conn.createStatement();
        String sqlUp = "SELECT tradeTime,count(*) as cnt\n" +
                "FROM \"stock_stream_increase\"\n" +
                "WHERE \"__time\" >= CURRENT_TIMESTAMP - INTERVAL '7' DAY\n" +
                "AND CAST(increase as DOUBLE) > 0.1 \n" +
                "GROUP BY 1";
        String sqlDown = "SELECT tradeTime,count(*) as cnt\n" +
                "FROM \"stock_stream_increase\"\n" +
                "WHERE \"__time\" >= CURRENT_TIMESTAMP - INTERVAL '7' DAY\n" +
                "AND CAST(increase as DOUBLE) < -0.1\n" +
                "GROUP BY 1";
        ResultSet rs = stat.executeQuery(sqlUp);
        List<Map<String,Object>> upList = new ArrayList<>();
        List<Map<String,Object>> downList = new ArrayList<>();
        while (rs.next()){
            Map<String,Object> map = new HashMap<>();
            map.put("time",rs.getString("tradeTime"));
            map.put("count",rs.getString("cnt"));
            upList.add(map);
        }
        rs = stat.executeQuery(sqlDown);
        while (rs.next()){
            Map<String,Object> map = new HashMap<>();
            map.put("time",rs.getString("tradeTime"));
            map.put("count",rs.getString("cnt"));
            downList.add(map);
        }
        //关流
        DbUtil.close(rs,stat,conn);

        JSONObject jsonObject = new JSONObject();
        jsonObject.put("upList",upList);
        jsonObject.put("downList",downList);
        jsonObject.put("code",HttpCode.SUCC_200.getCode());

        return jsonObject;
    }

    /**
     * 今日与昨日成交量对比
     * @return
     * @throws SQLException
     */
    @Override
    public JSONObject compareTradeVol() throws SQLException {
        Connection conn = DbUtil.getConn(driverClassName, url);
        Statement stat = conn.createStatement();
        String todaySql = "SELECT TIME_FORMAT(eventTime,'HH:mm:ss') as tradeTime,SUM(PARSE_LONG(tradeVolDay)) as vol\n" +
                "FROM \"stock_stream_sse\"\n" +
                "WHERE \"__time\" >= CURRENT_TIMESTAMP - INTERVAL '5' DAY\n" +
                "GROUP BY 1";
        String yestodaySql = "SELECT TIME_FORMAT(eventTime,'HH:mm:ss') as tradeTime,SUM(PARSE_LONG(tradeVolDay)) as vol\n" +
                "FROM \"stock_stream_sse\"\n" +
                "WHERE \"__time\" >= CURRENT_TIMESTAMP - INTERVAL '5' DAY AND __time < CURRENT_TIMESTAMP - INTERVAL '4' DAY\n" +
                "GROUP BY 1 ";
        ResultSet rs = stat.executeQuery(todaySql);

        List<Map<String,Object>> volList = new ArrayList<>();
        while (rs.next()){
            Map<String,Object> map = new HashMap<>();
            map.put("time",rs.getString(1));
            map.put("count",rs.getString(2));
            volList.add(map);
        }
        rs = stat.executeQuery(yestodaySql);
        List<Map<String,Object>> yesVolList = new ArrayList<>();
        while (rs.next()){
            Map<String,Object> map = new HashMap<>();
            map.put("time",rs.getString(1));
            map.put("count",rs.getString(2));
            yesVolList.add(map);
        }
        DbUtil.close(rs,stat,conn);
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("volList",volList);
        jsonObject.put("yesVolList",yesVolList);
        return jsonObject;
    }


    //个股涨跌幅度查询
    @Override
    public QuotRes increaseRangeQuery() throws SQLException {
        Connection conn = DbUtil.getConn(driverClassName, url);
        Statement stat = conn.createStatement();
        String sql = "SELECT \n" +
                "\tCASE WHEN CAST(increase AS DOUBLE) >0.07 THEN '>7%'\n" +
                "\tWHEN CAST(increase AS DOUBLE)>0.05 AND  CAST(increase AS DOUBLE) <= 0.07 THEN '5~7%'\n" +
                "\tWHEN CAST(increase AS DOUBLE)>0.03 AND  CAST(increase AS DOUBLE) <= 0.05 THEN '3~5%'\n" +
                "\tWHEN CAST(increase AS DOUBLE)>0 AND  CAST(increase AS DOUBLE) <= 0.03 THEN '0~3%'\n" +
                "\tWHEN CAST(increase AS DOUBLE)>-0.03 AND  CAST(increase AS DOUBLE) <= 0 THEN '-3~0%'\n" +
                "\tWHEN CAST(increase AS DOUBLE)>-0.05 AND  CAST(increase AS DOUBLE) <= -0.03 THEN '-3~-5%'\n" +
                "\tWHEN CAST(increase AS DOUBLE)>-0.07 AND  CAST(increase AS DOUBLE) <= -0.05 THEN '-7~-5%'\n" +
                "\tELSE '-7%' END AS increase,COUNT(*) AS cnt\n" +
                "FROM \n" +
                "\t\"stock_stream_increase\"\n" +
                "WHERE \"__time\" >= CURRENT_TIMESTAMP - INTERVAL '19' DAY\n" +
                "GROUP BY \n" +
                "\tCASE WHEN CAST(increase AS DOUBLE) >0.07 THEN '>7%'\n" +
                "\tWHEN CAST(increase AS DOUBLE)>0.05 AND  CAST(increase AS DOUBLE) <= 0.07 THEN '5~7%'\n" +
                "\tWHEN CAST(increase AS DOUBLE)>0.03 AND  CAST(increase AS DOUBLE) <= 0.05 THEN '3~5%'\n" +
                "\tWHEN CAST(increase AS DOUBLE)>0 AND  CAST(increase AS DOUBLE) <= 0.03 THEN '0~3%'\n" +
                "\tWHEN CAST(increase AS DOUBLE)>-0.03 AND  CAST(increase AS DOUBLE) <= 0 THEN '-3~0%'\n" +
                "\tWHEN CAST(increase AS DOUBLE)>-0.05 AND  CAST(increase AS DOUBLE) <= -0.03 THEN '-3~-5%'\n" +
                "\tWHEN CAST(increase AS DOUBLE)>-0.07 AND  CAST(increase AS DOUBLE) <= -0.05 THEN '-7~-5%'\n" +
                "\tELSE '-7%' END ";
        ResultSet rs = stat.executeQuery(sql);
        List<Map<String,Object>> list = new ArrayList<>();
        while (rs.next()){
            Map<String,Object> map = new HashMap<>();
            map.put("count",rs.getString("cnt"));
            map.put("title",rs.getString("increase"));
            list.add(map);
        }
        DbUtil.close(rs,stat,conn);
        QuotRes quotRes = new QuotRes();
        quotRes.setItems(list);
        quotRes.setCode(HttpCode.SUCC_200.getCode());
        return quotRes;
    }


    //个股分时列表数据
    @Override
    public QuotRes stockAll() throws SQLException {
        Connection conn = DbUtil.getConn(driverClassName, url);
        Statement stat = conn.createStatement();
        String sql = "SELECT secCode,secName,preClosePrice,openPrice,closePrice,highPrice,lowPrice,tradeAmt,tradeVol,tradeAmtDay,tradeVolDay\n" +
                "FROM \"stock_stream_sse\"\n" +
                "WHERE \"__time\" >= CURRENT_TIMESTAMP - INTERVAL '7' DAY\n" +
                "ORDER BY __time desc \n" +
                "LIMIT 100";
        List<Map<String,Object>> list = new ArrayList<>();
        ResultSet rs = stat.executeQuery(sql);
        while (rs.next()){
            Map<String,Object> map = new HashMap<>();
            map.put("code",rs.getString(1));
            map.put("name",rs.getString(2));
            map.put("preClosePrice",rs.getString(3));
            map.put("openPrice",rs.getString(4));
            map.put("tradePrice",rs.getString(5));
            map.put("highPrice",rs.getString(6));
            map.put("lowPrice",rs.getString(7));
            map.put("tradeAmt",rs.getString(8));
            map.put("tradeVol",rs.getString(9));
            map.put("tradeVolDay",rs.getString(10));
            map.put("tradeAmtDay",rs.getString(11));
            list.add(map);
        }
        DbUtil.close(rs,stat,conn);
        QuotRes quotRes = new QuotRes();
        quotRes.setItems(list);
        quotRes.setCode(HttpCode.SUCC_200.getCode());
        return quotRes;
    }


    //个股模糊查询
    @Override
    public QuotRes searchCode(String searchStr) throws SQLException {
        Connection conn = DbUtil.getConn(driverClassName, url);
        Statement stat = conn.createStatement();
        String sql = "SELECT DISTINCT secCode,secName\n" +
                "FROM \"stock_stream_sse\"\n" +
                "WHERE \"__time\" >= CURRENT_TIMESTAMP - INTERVAL '7' DAY \n" +
                "AND secCode LIKE '%"+searchStr+"%'\n" +
                "LIMIT 10";
        ResultSet rs = stat.executeQuery(sql);
        List<Map<String,Object>> list = new ArrayList<>();
        while (rs.next()){
            Map<String,Object> map = new HashMap<>();
            map.put("code",rs.getString(1));
            map.put("name",rs.getString(2));
            list.add(map);
        }
        DbUtil.close(rs,stat,conn);
        QuotRes quotRes = new QuotRes();
        quotRes.setItems(list);
        quotRes.setCode(HttpCode.SUCC_200.getCode());
        return quotRes;
    }

    //个股分时详情数据
    @Override
    public QuotRes timeSharingQuery(String code) throws SQLException {
        Connection conn = DbUtil.getConn(driverClassName, url);
        Statement stat = conn.createStatement();
        String sql = "SELECT secCode,secName,preClosePrice,openPrice,closePrice,\n" +
                "      highPrice,lowPrice,tradeAmt,tradeVol,tradeVolDay,tradeAmtDay,tradeTime\n" +
                "FROM \"stock_stream_sse\"\n" +
                "WHERE \"__time\" >= CURRENT_TIMESTAMP - INTERVAL '7' DAY\n" +
                "AND secCode='"+code+"'";
        ResultSet rs = stat.executeQuery(sql);
        List<Map<String,Object>> list = new ArrayList<>();
        while (rs.next()){
            Map<String,Object> map = new HashMap<>();
            map.put("code",rs.getString(1));
            map.put("name",rs.getString(2));
            map.put("preClosePrice",rs.getString(3));
            map.put("openPrice",rs.getString(4));
            map.put("tradePrice",rs.getString(5));
            map.put("highPrice",rs.getString(6));
            map.put("lowPrice",rs.getString(7));
            map.put("tradeAmt",rs.getString(8));
            map.put("tradeVol",rs.getString(9));
            map.put("tradeVolDay",rs.getString(10));
            map.put("tradeAmtDay",rs.getString(11));
            map.put("date",rs.getString(12));
            list.add(map);
        }
        DbUtil.close(rs,stat,conn);
        QuotRes quotRes = new QuotRes();
        quotRes.setItems(list);
        quotRes.setCode(HttpCode.SUCC_200.getCode());
        return quotRes;
    }

    //个股日K
    @Override
    public QuotRes dklineQuery(String code) throws SQLException {
        List<Map<String, Object>> list = quotMapper.dklineQuery(code);
        QuotRes quotRes = new QuotRes();
        quotRes.setItems(list);
        quotRes.setCode(HttpCode.SUCC_200.getCode());
        return quotRes;
    }

    //最新个股分时行情数据
    @Override
    public JSONObject stockMinDetail(String code) throws SQLException {
        Connection conn = DbUtil.getConn(driverClassName, url);
        Statement stat = conn.createStatement();
        String sql = "SELECT preClosePrice,openPrice,closePrice,highPrice,lowPrice,tradeAmt,tradeVol\n" +
                "FROM \"stock_stream_sse\"\n" +
                "WHERE \"__time\" >= CURRENT_TIMESTAMP - INTERVAL '10' DAY\n" +
                "AND secCode = '"+code+"'\n" +
                "ORDER BY __time DESC\n" +
                "LIMIT 1";
        ResultSet rs = stat.executeQuery(sql);
        JSONObject jsonObject = new JSONObject();
        while (rs.next()){
            jsonObject.put("preClosePrice",rs.getString(1));
            jsonObject.put("openPrice",rs.getString(2));
            jsonObject.put("tradePrice",rs.getString(3));
            jsonObject.put("highPrice",rs.getString(4));
            jsonObject.put("lowPrice",rs.getString(5));
            jsonObject.put("tradeAmt",rs.getString(6));
            jsonObject.put("tradeVol",rs.getString(7));
        }
        DbUtil.close(rs,stat,conn);
        jsonObject.put("code",HttpCode.SUCC_200.getCode());
        return jsonObject;
    }


    //单个个股秒级行情数据查询，查询当前分钟内的秒级数据
    @Override
    public QuotRes stockSecQuery(String code) throws SQLException {
        /**
         * 1.获取起止rowkey
         * 2.区间查询
         * 3.获取指定列数据
         * 4.封装返回数据
         */
        //获取最新秒级时间
        //map.put("startSecTime", startMin);
        //map.put("endSecTime", endMin);
        Map<String, String> curSecTime = DateUtil.getCurSecTime();
        String startSecTime = curSecTime.get("startSecTime");
        String endSecTime = curSecTime.get("endSecTime");
        String startRowkey = code+startSecTime;
        String endRowkey = code+endSecTime;

        List<String> listValue = HbaseUtil.scanQuery("quot_stock", "info", "data", startRowkey, endRowkey);
        List<Map<String,Object>> list = new ArrayList<>();
        for (String str : listValue) {
            //3.获取指定列数据
            JSONObject jsonObject = JSONObject.parseObject(str);
            Map<String,Object> map = new HashMap<>();
            //map.put("tradeTime",jsonObject.getString("tradeTime").substring(8,14));
            map.put("tradeTime",DateUtil.formatSecTime(jsonObject.getLong("eventTime")));
            map.put("tradePrice",jsonObject.getDouble("closePrice"));
            map.put("tradeVol",jsonObject.getInteger("tradeVolDay"));
            map.put("tradeAmt",jsonObject.getDouble("tradeAmtDay"));
            list.add(map);
        }
        QuotRes quotRes = new QuotRes();
        quotRes.setItems(list);
        quotRes.setCode(HttpCode.SUCC_200.getCode());
        return quotRes;
    }


    //个股主营业务描述
    @Override
    public JSONObject stockDesc(String code) throws SQLException {
        Map<String, Object> map = quotMapper.stockDesc(code);
        JSONObject jsonObject = (JSONObject) JSON.toJSON(map);
        return jsonObject;
    }

    @Override
    public QuotRes externalQuery() throws SQLException {
        List<Map<String, Object>> list = quotMapper.externalQuery();
        QuotRes quotRes = new QuotRes();
        quotRes.setItems(list);
        quotRes.setCode(HttpCode.SUCC_200.getCode());
        return quotRes;
    }
}
